CREATE PROCEDURE [dbo].[BAESearchProductsWithCategory]
@SearchString VARCHAR(200),
@MatchType INT

AS

-- SeachString:  String of 1 or more search terms, all separated by spaces
-- MatchType: 0=match any, 1 =match all, 2 = exact match of entire expression only
-- Results are returned in order of relevance

DECLARE @i1 INT;
DECLARE @i2 INT;
DECLARE @Word VARCHAR(100);
DECLARE @Words TABLE (Word VARCHAR(100) NOT NULL);
DECLARE @ProductsWithCategory TABLE (
    OrderProductID    INT,
    Title            VARCHAR(60),
    Description        VARCHAR(4096),
    IsSuperProduct    BIT,
    ProductCode        VARCHAR(31),
    SellOnWeb        INT,
    IsKit            BIT,
    OrderCategoryID    INT
)

INSERT INTO @ProductsWithCategory
SELECT DISTINCT OrderProduct.OrderProductID, p.TITLE COLLATE database_default AS Title, (CAST(p.WEB_DESC AS varchar(4096))) Description, IsSuperProduct, ProductCode, p.WEB_OPTION AS SellOnWeb, p.IS_KIT AS IsKit,
        (SELECT    TOP 1 OrderCategoryID FROM OrderProductCategoryLookup cat WHERE cat.OrderProductID = OrderProduct.OrderProductID) AS OrderCategoryID
    FROM     OrderProduct INNER JOIN Product p
        ON p.PRODUCT_CODE COLLATE database_default = OrderProduct.ProductCode COLLATE database_default
    WHERE     ((IsSuperProduct = 0 AND p.WEB_OPTION > 0))  AND (SELECT TOP 1 OrderCategoryID FROM OrderProductCategoryLookup cat WHERE cat.OrderProductID = OrderProduct.OrderProductID) IS NOT NULL
    UNION
    SELECT DISTINCT op.OrderProductID, op.Title  COLLATE database_default AS Title, op.Description  COLLATE database_default, op.IsSuperProduct, op.ProductCode, op.SellOnWeb, CAST('0' AS bit)  AS IsKit,
        (SELECT TOP 1  OrderCategoryID FROM OrderProductCategoryLookup cat WHERE cat.OrderProductID = op.OrderProductID) AS OrderCategoryID
    FROM     OrderProduct op
    WHERE     IsSuperProduct = 1 AND op.SellOnWeb > 0 AND (SELECT TOP 1  OrderCategoryID FROM OrderProductCategoryLookup cat WHERE cat.OrderProductID = op.OrderProductID) IS NOT NULL

DECLARE @WordCount AS integer;

SET NOCOUNT ON

-- Parse the SearchString to extract all words:

IF (@MatchType != 2)
    BEGIN
        SET @SearchString = ' ' + @SearchString  + ' ';
        SET @i1 = 1;

        WHILE (@i1 != 0)
            BEGIN
                SET @i2=CHARINDEX(' ', @SearchString, @i1+1)
                IF (@i2 != 0)
                    BEGIN
                      SET @Word = RTRIM(LTRIM(SUBSTRING(@SearchString, @i1+1, @i2-@i1)))
                      IF @Word != '' INSERT INTO @Words SELECT @Word
                    END
                SET @i1 = @i2
            END
        END
ELSE

    INSERT INTO @Words SELECT LTRIM(RTRIM(@SearchString))

-- Get the total # of words:

set @WordCount = (select count(*) from @Words)

-- Return Results in order of relevance:

SELECT a.MatchPct, T.*
FROM @ProductsWithCategory T
INNER JOIN
(
    SELECT T.OrderProductID, COUNT(*)  * 1.0 / @WordCount AS MatchPct
    FROM @ProductsWithCategory T
    INNER JOIN
        @Words W on ' ' + LOWER(T.Title) + ' ' LIKE '%[^a-z]' + LOWER(Word) + '[^a-z]%'
        OR ' ' + LOWER(T.Description) + ' ' LIKE '%[^a-z]' + LOWER(Word) + '[^a-z]%'
        OR dbo.SearchForSuperProduct(T.OrderProductID, T.IsSuperProduct, @SearchString, @MatchType) = 1
    GROUP BY T.OrderProductID
) a ON T.OrderProductID = a.OrderProductID
WHERE
    MatchPct = 1 or @MatchType <>1
ORDER BY
    T.Title

GO